package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dao.Connector;
import java.util.logging.Level;
import java.util.logging.Logger;
@SuppressWarnings("unused")

public class Room {
	Connection connect =null;
	PreparedStatement pstm=null;
	private String table="tblroom";
	
	public boolean addRoom(String name, int type , int status){
		connect = Connector.getConnect();
		if (connect == null)
			System.out.println("null");
		try{
			pstm= connect.prepareStatement("INSERT INTO "+ table+"(name,type,status) VALUES (?,?,?)");
			pstm.setString(1,name);
			pstm.setInt(2, type);
			pstm.setInt(3, status);
			if(pstm.executeUpdate()>0){
				return true;
			}
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean deleteRoom(int id){
		connect = Connector.getConnect();
		try{
			pstm = connect.prepareStatement("DELETE FROM "+this.table+ " WHERE id = ?");
			pstm.setInt(1, id);
			if(pstm.executeUpdate()>0){
				return true;
			}
		}catch(SQLException e){
			
		}
		return false;
	}
	public boolean updateRoom(int id, String name, int type,int status){
		connect= Connector.getConnect();
		try{
			pstm = connect.prepareStatement("UPDATE "+this.table +" SET name=?," +
					"type=?,status=?  WHERE id=?");
			pstm.setString(1, name);
			pstm.setInt(2, type);
			pstm.setInt(3, status);
			pstm.setInt(4,id);
			if(pstm.executeUpdate()>0){
				return true;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
	}
        public boolean updateStatus(int id,int status){
            connect= Connector.getConnect();
		try{
			pstm = connect.prepareStatement("UPDATE "+this.table +" SET status=? WHERE id=?");
			pstm.setInt(1, status);			
			pstm.setInt(2, id);
			if(pstm.executeUpdate()>0){
				return true;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
            
        }
	public  ArrayList<entity.Room> getAllRoom(){
		connect= Connector.getConnect();
		ArrayList<entity.Room> list = new ArrayList<>();
		
		ResultSet rs=null;
		try{
			pstm= connect.prepareStatement("Select *from "+this.table);			
			rs=pstm.executeQuery();
                        dao.RoomType type;
			while(rs.next()){
                            type = new dao.RoomType();
				entity.Room room = new entity.Room();
				room.setId(rs.getInt("id"));
				room.setName(rs.getString("name"));
				room.setType(rs.getInt("type"));
				room.setStatus(rs.getInt("status"));
                                room.setRoomtype(type.getTypeRoomById(room.getType())); 
				list.add(room);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return list;
	}
	public entity.Room getRoomById(int id){
		
		connect= Connector.getConnect();
		ResultSet rs=null;
		entity.Room room = null;
		
		try{
			pstm= connect.prepareStatement("Select *from "+this.table+" where id=?");
			pstm.setInt(1, id);
			rs= pstm.executeQuery();
                        RoomType typeRoom;
		if(rs.next()){
			room =  new entity.Room();
                        typeRoom = new RoomType();
			room.setId(rs.getInt("id"));
			room.setName(rs.getString("name"));
			room.setStatus(rs.getInt("status"));
			room.setType(rs.getInt("type"));
                        room.setRoomtype(typeRoom.getTypeRoomById(room.getType())); 
		}
	
		}catch(SQLException e){
			e.printStackTrace();
		}
		return room;
	}
	
	public void getIdByName(String name){
		connect= Connector.getConnect();
		ResultSet rs=null;
		entity.Room room = new entity.Room();
		try{
			pstm= connect.prepareStatement("Select *from "+this.table+" where name=?");
			pstm.setString(1,name);
			rs= pstm.executeQuery();
			while(rs.next()){

				room.setId(rs.getInt("id"));
                                
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
        public ArrayList<entity.Room> getAllRoomByType(int type ){
            	connect= Connector.getConnect();
		ArrayList<entity.Room> list = new ArrayList<entity.Room>();		
		ResultSet rs=null;
		try{
			pstm= connect.prepareStatement("Select *from "+this.table+" where type=?");			
			pstm.setInt(1,type);
                        rs=pstm.executeQuery();	
			while(rs.next()){
				entity.Room room = new entity.Room();
				room.setId(rs.getInt("id"));
				room.setName(rs.getString("name"));
				room.setType(rs.getInt("type"));
				room.setStatus(rs.getInt("status"));
				list.add(room);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return list;
        }
        public ArrayList<entity.Room> getAllRoomByStatus(int status){
            connect = Connector.getConnect();
            ArrayList<entity.Room> list = new ArrayList<entity.Room>();
            try{
                pstm = connect.prepareStatement("Select * from "+ this.table + "where status=?");
                pstm.setInt(1, status);
                ResultSet rs = pstm.executeQuery();
                
                while(rs.next()){
                    entity.Room room = new entity.Room();
                    room.setId(rs.getInt("id"));
		    room.setName(rs.getString("name"));
		    room.setType(rs.getInt("type"));
		    room.setStatus(rs.getInt("status"));
		    list.add(room);
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
            return list;
        }
        public ArrayList<entity.Room> getAllRoomByTypeAndStatus(int type, int status){
            connect = Connector.getConnect();
            ArrayList<entity.Room> list = new ArrayList<entity.Room>();
            try{
                pstm = connect.prepareStatement("Select * from "+ this.table + " where type=? and status=?");
                pstm.setInt(1, type);
                pstm.setInt(2, status);
                ResultSet rs = pstm.executeQuery();
                RoomType typeRoom;
                while(rs.next()){
                    typeRoom = new RoomType();
                    entity.Room room = new entity.Room();
                    room.setId(rs.getInt("id"));
		    room.setName(rs.getString("name"));
		    room.setType(rs.getInt("type"));
		    room.setStatus(rs.getInt("status"));
                    room.setRoomtype(typeRoom.getTypeRoomById(room.getType())); 
		    list.add(room);
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
            return list;
        }
      public boolean isRoomNameExist(int id, String nameRoom){
      
           connect = Connector.getConnect();
            try{
                pstm = connect.prepareStatement("Select * from "+ this.table + " where name=?");
                pstm.setString(1,nameRoom);
                ResultSet rs = pstm.executeQuery();
                if(rs.next()){
                    if(rs.getInt("id")!=id)
                   return true;
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
          return false;
      }
      public int  countByStatus(int status){
          
           connect = Connector.getConnect();
            try{
                pstm = connect.prepareStatement("Select count(*) as countid from "+ this.table + " where status=?");
                pstm.setInt(1,status);
                ResultSet rs = pstm.executeQuery();
                if(rs.next())
                return rs.getInt("countid");
            }catch(SQLException e){
                e.printStackTrace();
            }
          return 0;
      }
	
}

